from openpyxl import load_workbook
import xlrd
from utils.LogHandler import logger
from conf import settings
import json


class ExcelHandler(object):
    """
        获取excel的测试数据
        excel格式为第一行用例标题
            'case_num', 'title', 'url', 'method', 'params', 'data', 'json', 'cookies', 'headers', 'except'
        后面的行对应标题的数据内容，参数可为空
        get_data_openpyxl：使用openpyxl模块获取
        get_data_xlrd： 使用xlrd模块获取，xlrd需要旧版本，比如1.0.2
    """

    def __init__(self, file_path, sheet_index=0):
        self.file_path = file_path
        self.sheet_index = sheet_index

    def get_data_openpyxl(self):
        try:
            wb_openpyxl = load_workbook(self.file_path)
            sheet = wb_openpyxl.worksheets[self.sheet_index]
            test_cases = []
            for row in range(sheet.max_row):
                cases = []
                for column in range(sheet.max_column):
                    content = sheet.cell(row + 1, column + 1).value
                    if row == 0:
                        continue
                    cases.append(content)
                if cases:
                    dict_data = dict(zip(settings.EXCEL_TITLE_LIST, cases))
                    test_cases.append(dict_data)
            logger.info("获取数据成功")
            return test_cases
        except Exception as e:
            logger.error(f"获取数据失败：{e}")

    def get_data_xlrd(self):
        try:
            wb_xlrd = xlrd.open_workbook(self.file_path)
            sheet = wb_xlrd.sheet_by_index(self.sheet_index)
            test_cases = []
            for row in range(1, sheet.nrows):
                dict_data = dict(zip(settings.EXCEL_TITLE_LIST, sheet.row_values(row)))
                test_cases.append(dict_data)
            logger.info("获取数据成功")
            return test_cases
        except Exception as e:
            logger.error(f"获取数据失败：{e}")


